'=====================================================================
'
'  File:		AdoSourceSample.cs
'  Summary:	Demonstrates a data flow source component. This component uses the Ado.Net connectionmanager
'				to connect to Sql Server Databases. 
'				
'  Date:		6152004
'
'---------------------------------------------------------------------
'
'  This file is part of the Microsoft SQL Server Code Samples.
'  Copyright (C) Microsoft Corporation.  All rights reserved.
'
'This source code is intended only as a supplement to Microsoft
'Development Tools andor on-line documentation.  See these other
'materials for detailed information regarding Microsoft code samples.
'
'THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY ANDOR FITNESS FOR A
'PARTICULAR PURPOSE.
'
'===================================================================== 
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime


<DtsPipelineComponent(DisplayName:="Ado Source VB", ComponentType:=ComponentType.SourceAdapter, IconResource:="Microsoft.Samples.SqlServer.Dts.AdoSource.ico")> _
Public Class AdoSourceSample
    Inherits PipelineComponent
    Private columnInformation As ArrayList
    Private Cancel As Boolean
    Private isConnected As Boolean
    Private oledbConnection As OleDbConnection
    Private oledbCommand As OleDbCommand
    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1823:AvoidUnusedPrivateFields")> _
    Private outputColumnsAreValid As Boolean = True
    Private areExternalMetaDataColumnsValid As Boolean = True
    Private doOutputColumnsMatchExternalMetaDataColumns As Boolean

    Private Structure ColumnInfo
        Public BufferColumnIndex As Integer
        Public ColumnName As String
    End Structure

    Public Overloads Overrides Sub ProvideComponentProperties()
        ComponentMetaData.RuntimeConnectionCollection.RemoveAll()
        RemoveAllInputsOutputsAndCustomProperties()
        Dim sql As IDTSCustomProperty100 = ComponentMetaData.CustomPropertyCollection.New
        sql.Name = "SqlStatement"
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection.New
        output.Name = "AdoSourceOutput"
        output.ExternalMetadataColumnCollection.IsUsed = True
        Dim adoConnection As IDTSRuntimeConnection100 = ComponentMetaData.RuntimeConnectionCollection.New
        adoConnection.Name = "ADO Connection"
    End Sub

    <CLSCompliant(False)> _
    Public Overloads Overrides Function Validate() As DTSValidationStatus
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection(0)
        If Not (ComponentMetaData.InputCollection.Count = 0) Then
            ComponentMetaData.FireError(0, ComponentMetaData.Name, "Has an input when no input should exist.", "", 0, Cancel)
            Return DTSValidationStatus.VS_ISCORRUPT
        End If
        If ComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager Is Nothing Then
            ComponentMetaData.FireError(0, ComponentMetaData.Name, "No ADO ConnectionManager specified.", "", 0, Cancel)
            Return DTSValidationStatus.VS_ISBROKEN
        End If
        Dim sqlStatement As IDTSCustomProperty100 = ComponentMetaData.CustomPropertyCollection("SqlStatement")
        If sqlStatement.Value Is Nothing OrElse CType(sqlStatement.Value, String).Length = 0 Then
            ComponentMetaData.FireError(0, ComponentMetaData.Name, "SqlStatement not specified.", "", 0, Cancel)
            Return DTSValidationStatus.VS_ISBROKEN
        Else
            If isConnected Then
                GetSchemaDataTable()
            End If
        End If
        If ComponentMetaData.OutputCollection(0).OutputColumnCollection.Count = 0 Then
            Return DTSValidationStatus.VS_NEEDSNEWMETADATA
        End If
        If ComponentMetaData.ValidateExternalMetadata Then
            If Not AreOutputColumnsValid Then
                outputColumnsAreValid = False
                ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "The output columns do not match the external data source.", "", 0)
                Return DTSValidationStatus.VS_NEEDSNEWMETADATA
            End If
            If Not ComponentValidation.DoesExternalMetaDataMatchOutputMetaData(output) Then
                areExternalMetaDataColumnsValid = False
                ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "The ExternalMetaDataColumns do not match the output columns.", "", 0)
                Return DTSValidationStatus.VS_NEEDSNEWMETADATA
            End If
            areExternalMetaDataColumnsValid = True
            outputColumnsAreValid = True
        Else
            If Not ComponentValidation.DoesOutputColumnMetaDataMatchExternalColumnMetaData(ComponentMetaData.OutputCollection(0)) Then
                doOutputColumnsMatchExternalMetaDataColumns = False
                ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "Output columns do not match external metadata.", "", 0)
                Return DTSValidationStatus.VS_NEEDSNEWMETADATA
            End If
            doOutputColumnsMatchExternalMetaDataColumns = True
        End If
        Return MyBase.Validate
    End Function

    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes")> _
    Public Overloads Overrides Sub AcquireConnections(ByVal transaction As Object)
        If Not (ComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager Is Nothing) Then
            Dim cm As ConnectionManager = Microsoft.SqlServer.Dts.Runtime.DtsConvert.GetWrapper(ComponentMetaData.RuntimeConnectionCollection(0).ConnectionManager)
            Dim cmado As ConnectionManagerAdoNet = CType(cm.InnerObject, ConnectionManagerAdoNet)
            If cmado Is Nothing Then
                Throw New Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.")
            End If

            oledbConnection = CType(cmado.AcquireConnection(transaction), OleDbConnection)
            If oledbConnection Is Nothing Then
                Throw New Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.")
            End If

            isConnected = True
        End If
    End Sub

    Public Overloads Overrides Sub ReleaseConnections()
        If Not (oledbConnection Is Nothing) AndAlso Not (oledbConnection.State = ConnectionState.Closed) Then
            oledbConnection.Close()
        End If
        isConnected = False
    End Sub

    Public Overloads Overrides Sub ReinitializeMetaData()
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection(0)
        If isConnected Then
            If output.OutputColumnCollection.Count = 0 Then
                Me.CreateColumnsFromDataTable()
            End If
            If Not AreOutputColumnsValid() Then
                FixOutputColumns()
                outputColumnsAreValid = True
            End If
            If Not doOutputColumnsMatchExternalMetaDataColumns Then
                doOutputColumnsMatchExternalMetaDataColumns = True
                output.ExternalMetadataColumnCollection.RemoveAll()
                For Each col As IDTSOutputColumn100 In output.OutputColumnCollection
                    CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, col)
                Next
            End If
            If Not areExternalMetaDataColumnsValid Then
                ComponentValidation.FixExternalMetaDataColumns(output)
                areExternalMetaDataColumnsValid = True
            End If
        Else
            If Not doOutputColumnsMatchExternalMetaDataColumns Then
                ComponentValidation.FixOutputColumnMetaData(output)
                doOutputColumnsMatchExternalMetaDataColumns = True
            End If
        End If
    End Sub

    <CLSCompliant(False)> _
    Public Overloads Overrides Function MapOutputColumn(ByVal iOutputID As Integer, ByVal iOutputColumnID As Integer, ByVal iExternalMetadataColumnID As Integer, ByVal bMatch As Boolean) As IDTSExternalMetadataColumn100
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection.GetObjectByID(iOutputID)
        Dim outputColumn As IDTSOutputColumn100 = output.OutputColumnCollection.GetObjectByID(iOutputColumnID)
        Dim externalColumn As IDTSExternalMetadataColumn100 = output.ExternalMetadataColumnCollection.GetObjectByID(iExternalMetadataColumnID)
        outputColumn.ExternalMetadataColumnID = externalColumn.ID
        Return externalColumn
    End Function

    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes")> _
    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:DoNotPassLiteralsAsLocalizedParameters", MessageId:="System.Exception.#ctor(System.String)")> _
    <CLSCompliant(False)> _
    Public Overloads Overrides Function SetOutputColumnProperty(ByVal outputID As Integer, ByVal outputColumnID As Integer, ByVal propertyName As String, ByVal propertyValue As Object) As IDTSCustomProperty100
        If propertyName = "DataSourceColumnName" Then
            Throw New Exception("The DataSourceColumnName can not be modified.")
        End If

        Return MyBase.SetOutputColumnProperty(outputID, outputColumnID, propertyName, propertyValue)
    End Function

    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes")> _
    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:DoNotPassLiteralsAsLocalizedParameters", MessageId:="System.Exception.#ctor(System.String)")> _
    <CLSCompliant(False)> _
    Public Overloads Overrides Sub SetOutputColumnDataTypeProperties(ByVal iOutputID As Integer, ByVal iOutputColumnID As Integer, ByVal eDataType As DataType, ByVal iLength As Integer, ByVal iPrecision As Integer, ByVal iScale As Integer, ByVal iCodePage As Integer)
        Dim col As IDTSOutputColumn100 = ComponentMetaData.OutputCollection.GetObjectByID(iOutputID).OutputColumnCollection.GetObjectByID(iOutputColumnID)
        If Not (eDataType = col.DataType) Then
            Throw New Exception("The column data type is set based on the external column. It cannot be changed.")
        End If

        col.SetDataTypeProperties(eDataType, iLength, iPrecision, iScale, iCodePage)
    End Sub

    Public Overloads Overrides Sub PreExecute()
        Me.columnInformation = New ArrayList
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection(0)
        For Each col As IDTSOutputColumn100 In output.OutputColumnCollection
            Dim ci As ColumnInfo = New ColumnInfo
            ci.BufferColumnIndex = BufferManager.FindColumnByLineageID(output.Buffer, col.LineageID)
            ci.ColumnName = CType(col.CustomPropertyCollection("DataSourceColumnName").Value, String)
            columnInformation.Add(ci)
        Next

        oledbCommand = oledbConnection.CreateCommand
        oledbCommand.CommandType = CommandType.Text
        oledbCommand.CommandText = CType(ComponentMetaData.CustomPropertyCollection("SqlStatement").Value, String)
    End Sub

    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1804:RemoveUnusedLocals", MessageId:="output")> _
    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")> _
    Public Overloads Overrides Sub PrimeOutput(ByVal outputs As Integer, ByVal outputIDs As Integer(), ByVal buffers As PipelineBuffer())
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection(0)
        Dim buffer As PipelineBuffer = buffers(0)
        Try
            Dim dataReader As OleDbDataReader = oledbCommand.ExecuteReader
            While dataReader.Read
                buffer.AddRow()
                Dim x As Integer = 0
                While x < columnInformation.Count
                    Dim ci As ColumnInfo = CType(columnInformation(x), ColumnInfo)
                    Dim ordinal As Integer = dataReader.GetOrdinal(ci.ColumnName)
                    If dataReader.IsDBNull(ordinal) Then
                        buffer.SetNull(ci.BufferColumnIndex)
                    Else
                        buffer(ci.BufferColumnIndex) = dataReader(ci.ColumnName)
                    End If
                    System.Math.Min(System.Threading.Interlocked.Increment(x), x - 1)
                End While
            End While
        Catch
        Finally
            buffer.SetEndOfRowset()
        End Try
    End Sub

    Private Function AreOutputColumnsValid() As Boolean
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection(0)
        Dim dataTable As DataTable = GetSchemaDataTable
        If Not (dataTable.Rows.Count = output.OutputColumnCollection.Count) Then
            Return False
        End If
        Dim x As Integer = 0
        While x < output.OutputColumnCollection.Count
            Dim column As IDTSOutputColumn100 = output.OutputColumnCollection(x)
            Dim datasourceColumnName As IDTSCustomProperty100 = column.CustomPropertyCollection("DataSourceColumnName")
            Dim index As Integer = ColumnExists(dataTable, CType(datasourceColumnName.Value, String))
            If index = -1 OrElse Not DataSourceColumnMatchesOutputColumn(column, dataTable.Rows(index)) Then
                ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "The output column " + column.IdentificationString + " does not match the data source.", "", 0)
                Return False
            End If
            System.Math.Min(System.Threading.Interlocked.Increment(x), x - 1)
        End While
        Return True
    End Function

    Private Sub FixOutputColumns()
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection(0)
        Dim dt As DataTable = GetSchemaDataTable
        Dim columnsToDelete As ArrayList = New ArrayList
        Dim x As Integer = 0
        While x < output.OutputColumnCollection.Count
            Dim col As IDTSOutputColumn100 = output.OutputColumnCollection(x)
            Dim dataSourceColumnName As IDTSCustomProperty100 = col.CustomPropertyCollection("DataSourceColumnName")
            Dim index As Integer = ColumnExists(dt, CType(dataSourceColumnName.Value, String))
            If Not (index = -1) Then
                If Not Me.DataSourceColumnMatchesOutputColumn(col, dt.Rows(index)) Then
                    Dim dataType As DataType = New DataType
                    Dim length As Integer = 0
                    Dim precision As Integer = 0
                    Dim scale As Integer = 0
                    Dim codepage As Integer = 0
                    GetDataSourceColumnProperties(dataType, length, scale, precision, codepage, dt.Rows(index))
                    col.SetDataTypeProperties(dataType, length, precision, scale, codepage)
                End If
            Else
                columnsToDelete.Add(col.ID)
            End If
            System.Math.Min(System.Threading.Interlocked.Increment(x), x - 1)
        End While

        For Each o As Object In columnsToDelete
            output.OutputColumnCollection.RemoveObjectByID(CType(o, Integer))
        Next

        For Each row As DataRow In dt.Rows
            If Not IsDataSourceColumnFound(CType(row("ColumnName"), String)) Then
                CreateOutputColumn(row)
            End If
        Next
    End Sub

    Private Function IsDataSourceColumnFound(ByVal DataSourceColumn As String) As Boolean
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection(0)

        For Each col As IDTSOutputColumn100 In output.OutputColumnCollection
            If CType(col.CustomPropertyCollection("DataSourceColumnName").Value, String) = DataSourceColumn Then
                Return True
            End If
        Next

        Return False
    End Function

    Private Function DataSourceColumnMatchesOutputColumn(ByVal column As IDTSOutputColumn100, ByVal row As DataRow) As Boolean
        Dim dt As DataType = New DataType
        Dim length As Integer = 0
        Dim scale As Integer = 0
        Dim precision As Integer = 0
        Dim codepage As Integer = 0

        GetDataSourceColumnProperties(dt, length, scale, precision, codepage, row)
        If dt = column.DataType AndAlso length = column.Length AndAlso scale = column.Scale AndAlso codepage = column.CodePage Then
            Return True
        End If

        Return False
    End Function

    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")> _
    Private Function ColumnExists(ByVal dt As DataTable, ByVal ColumnName As String) As Integer
        Dim row As Integer = 0
        While row < dt.Rows.Count
            If CType(dt.Rows(row)("ColumnName"), String) = ColumnName Then
                Return row
            End If
            System.Math.Min(System.Threading.Interlocked.Increment(row), row - 1)
        End While

        Return -1
    End Function

    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes")> _
    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:DoNotPassLiteralsAsLocalizedParameters", MessageId:="System.Exception.#ctor(System.String)")> _
    Private Function GetSchemaDataTable() As DataTable
        If isConnected AndAlso Not (oledbConnection Is Nothing) Then
            Try
                Dim cmd As OleDbCommand = oledbConnection.CreateCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = CType(ComponentMetaData.CustomPropertyCollection("SqlStatement").Value, String)
                Dim schemaReader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
                Return schemaReader.GetSchemaTable
            Catch oleDbException As System.Data.OleDb.OleDbException
                ComponentMetaData.FireError(oleDbException.ErrorCode, ComponentMetaData.Name, "An OleDb exception occurred while retreiving the SchmaTable for """ + CType(ComponentMetaData.CustomPropertyCollection("SqlStatement").Value, String) + """.", "", 0, Cancel)
                For Each oError As OleDbError In oleDbException.Errors
                    ComponentMetaData.FireError(oError.NativeError, ComponentMetaData.Name, oError.Message, "", 0, Cancel)
                Next

                Throw
            End Try
        Else
            Throw New Exception("Unable to retrieve schema information from the data source either because the component is disconnected, or the connection is Nullable.")
        End If
    End Function

    Private Sub CreateColumnsFromDataTable()
        Dim output As IDTSOutput100 = ComponentMetaData.OutputCollection(0)
        output.OutputColumnCollection.RemoveAll()
        output.ExternalMetadataColumnCollection.RemoveAll()
        Dim dataTable As DataTable = GetSchemaDataTable
        For Each row As DataRow In dataTable.Rows
            CreateOutputColumn(row)
        Next
    End Sub

    Private Sub CreateOutputColumn(ByVal row As DataRow)
        Dim outColumn As IDTSOutputColumn100 = ComponentMetaData.OutputCollection(0).OutputColumnCollection.New
        Dim dataSourceColumnName As IDTSCustomProperty100 = outColumn.CustomPropertyCollection.New
        dataSourceColumnName.Name = "DataSourceColumnName"
        dataSourceColumnName.Value = row("ColumnName")
        dataSourceColumnName.Description = "The name of the column at the data source."
        Dim dt As DataType = New DataType
        Dim length As Integer = 0
        Dim precision As Integer = 0
        Dim scale As Integer = 0
        Dim codepage As Integer = 0
        GetDataSourceColumnProperties(dt, length, scale, precision, codepage, row)
        outColumn.Name = CType(row("ColumnName"), String)
        outColumn.SetDataTypeProperties(dt, length, precision, scale, codepage)
        CreateExternalMetaDataColumn(ComponentMetaData.OutputCollection(0).ExternalMetadataColumnCollection, outColumn)
    End Sub

    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")> _
    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")> _
    Private Sub GetDataSourceColumnProperties(ByRef dt As DataType, ByRef length As Integer, ByRef scale As Integer, ByRef precision As Integer, ByRef codepage As Integer, ByVal row As DataRow)
        Dim isLong As Boolean = False
        dt = AdoSourceSample.DataRecordTypeToBufferType(CType(row("DataType"), Type))
        dt = ConvertBufferDataTypeToFitManaged(dt, isLong)
        If CType(row("IsLong"), Boolean) = True Then
            If dt = DataType.DT_WSTR Then
                dt = DataType.DT_NTEXT
            End If
            If dt = DataType.DT_STR Then
                dt = DataType.DT_TEXT
            End If
            If dt = DataType.DT_BYTES Then
                dt = DataType.DT_IMAGE
            End If
        End If
        Select Case dt
            Case DataType.DT_STR, DataType.DT_TEXT, DataType.DT_BYTES
                length = CType(row("ColumnSize"), Integer)
                If Not (dt = DataType.DT_BYTES) Then
                    codepage = row.Table.Locale.TextInfo.ANSICodePage
                End If
                ' break 
            Case DataType.DT_WSTR
                length = CType(row("ColumnSize"), Integer)
                ' break 
            Case DataType.DT_NUMERIC
                Try
                    precision = CType(Convert.ToInt16(row("NumericPrecision"), _
                        System.Globalization.CultureInfo.InvariantCulture), Integer)
                Catch
                    precision = 28
                End Try
                Try
                    scale = CType(Convert.ToInt16(row("NumericScale"), _
                        System.Globalization.CultureInfo.InvariantCulture), Integer)
                Catch
                    scale = 255
                End Try
                If scale = 255 Then
                    If precision <= 14 Then
                        scale = precision
                        precision = scale * 2
                    Else
                        scale = 28 - precision
                        precision = 28
                        If scale < 5 Then
                            scale = 4
                        End If
                    End If
                End If
                ' break 
        End Select
    End Sub

    <System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")> _
    Private Sub CreateExternalMetaDataColumn(ByVal externalCollection As IDTSExternalMetadataColumnCollection100, ByVal column As IDTSOutputColumn100)
        Dim eColumn As IDTSExternalMetadataColumn100 = externalCollection.New
        eColumn.Name = column.Name
        eColumn.DataType = column.DataType
        eColumn.Precision = column.Precision
        eColumn.Length = column.Length
        eColumn.Scale = column.Scale
        column.ExternalMetadataColumnID = eColumn.ID
    End Sub
End Class